# -*- coding: utf-8 -*-
# !/usr/bin/env python

from django.db import connection
from django.conf import settings


def fetch_to_dict_pagetion(sql, params={}, page=1, page_size=15):
    page = int(page)
    page_size = int(page_size)
    sql_count = """select count(*) as count from (%s) _count""" % sql
    total_count = query_to_list(sql_count, params, True)
    sql_page = '%s limit %s,%s' % (sql, (page - 1) * page_size, page_size)
    result = query_to_list(sql_page, params)
    result_dict = {'results': result, 'count': total_count['count']}
    return result_dict


def query_to_list(sql, params=None, to_one_dict=False):
    ''' 查询所有结果返回字典类型数据
    :param
    sql: :param params:
    :return:
    '''
    with connection.cursor() as cursor:
        if params: cursor.execute(sql, params=params)
        else: cursor.execute(sql)

        col_names = [desc[0] for desc in cursor.description]
        row = cursor.fetchall()
        rowList = []
        for list in row:
            tMap = dict(zip(col_names, list))
            rowList.append(tMap)

        if to_one_dict:
            if len(rowList) == 1:
                return rowList[0]
            else:
                rowList = {}
        return rowList

def sql_encryption(str):
    """
    加密
    """
    sql = """
    select HEX(AES_ENCRYPT(%s, %s)) AS passwd;
    """
    return query_to_list(sql,[str, settings.APP_CONF["PASSKEY"]], True)['passwd']

    # passwd = query_to_list(sql,[unhex_str, settings.APP_CONF["PASSKEY"]], True)['passwd']
    # return str(passwd, encoding="utf-8")

def sql_decrypt(unhex_str):
    """
    解密
    """
    sql = """
    select AES_DECRYPT(UNHEX(%s), %s) AS passwd;
    """
    passwd = query_to_list(sql,[unhex_str, settings.APP_CONF["PASSKEY"]], True)['passwd']
    return str(passwd, encoding="utf-8")
